package com.insleton.admin.employee.sql;

public interface DepartmentSQL {

    /**
     * 员工和部门关联的SQL语句
     */
    String EMP_DEPT_SQL = " select e.id, e.name, e.dept_id, d.name dept_name, d.code from employee e, department d where e.dept_id = d.id ";
    // 可以不查询所有字段
    String EMP_DEPT_SQL_ALL = " select e.id, e.name, e.dept_id, d.name dept_name, d.code, d.create_date from employee e, department d where e.dept_id = d.id ";
    // SQL的参数顺序也是从索引1开始的.
    // 单独使用？问号也是可以的，不加索引
    String EMP_DEPT_SQL_PARAM = " select e.id, e.name, e.dept_id, d.name dept_name, d.code from employee e, department d where e.dept_id = d.id "
            + " and d.name = ?1";
    // SQL使用命名参数。SQL和HQL都可以使用命名参数
    String EMP_DEPT_SQL_NAMED_PARAM = " select e.id, e.name, e.dept_id, d.name dept_name, d.code from employee e, department d " +
            " where e.dept_id = d.id "
            + " and d.name = :name";

    String DEPT_SQL_PAGE = "select * from department d where name = :name";
    String DEPT_SQL_UNIQUE = "select * from department d where id = :id";
    String DEPT_SQL_UNIQUE_INDEX = "select * from department d where id = ?1";

    /**
     * 员工和部门关联的HQL语句。JPA实体之间没有配置关联关系，依然可以使用hql进行连接查询。
     */
    String EMP_DEPT_HQL = "select e.id, e.name, e.deptId, d.name deptName, d.code from Employee e, Department d where e.deptId = d.id";
    // HQL的参数索引从1开始
    String EMP_DEPT_HQL_PARAM = "select e.id, e.name, e.deptId, d.name deptName, d.code from Employee e, Department d where e.deptId = d.id"
            + " and d.name = ?1";
    // HQL命名参数
    String EMP_DEPT_HQL_NAMED_PARAM = "select e.id, e.name, e.deptId, d.name deptName, d.code from Employee e, Department d where e.deptId = d.id"
            + " and d.name = :name";

    String DEPT_HQL_PAGE = "select d from Department d where name = :name";
    String DEPT_HQL_UNIQUE = "select d from Department d where name = :name";
    String DEPT_HQL_UNIQUE_INDEX = "select d from Department d where name = ?1";
    String EMP_HQL_UNIQUE_INDEX = "select e from Employee e where name = ?1";
    String EMP_HQL_UNIQUE_INDEX_FIELD = "select e.id, e.name, e.state, e.deptId from Employee e where name = ?1";
    String EMP_HQL_UNIQUE_NAMED = "select e from Employee e where name = :name";
    String EMP_SQL_UNIQUE_INDEX = "select * from employee d where name = ?1";
    String EMP_SQL_UNIQUE_NAMED = "select * from employee d where name = :name";


}
